#!python3.9

"""
python2.7 pyodbc(3.8) error?
"""

import pyodbc  
import os
# os.environ.items()
"""
yum install python3-devel unixODBC unixODBC-devel

https://files.pythonhosted.org/packages/75/29/aa190749bac37ede0f11a68a75e7055254699c11572bd94213f1163dfd8f/pyodbc-4.0.27.tar.gz


ldd .lib/python3.5/site-packages/pyodbc.cpython-35m-x86_64-linux-gnu.so
ldd /home/gbase8s/lib/cli/iclis09b.so

python3.5 gbase8s_pyodbc.py
"""

# env
"""
# https://www.gbase.cn/community/post/3981

# \cp sqlhosts.gbase01 sqlhosts.odbc

export GBASEDBTDIR=/home/gbase8s

export DB_LOCALE=zh_CN.utf8
export CLIENT_LOCALE=zh_CN.utf8
export GL_USEGLU=1

export LD_LIBRARY_PATH=$GBASEDBTDIR/lib:$GBASEDBTDIR/lib/cli:$GBASEDBTDIR/lib/esql:$LD_LIBRARY_PATH

# export LD_LIBRARY_PATH=./python3/lib:$LD_LIBRARY_PATH

export GBASEDBTSQLHOSTS=$GBASEDBTDIR/etc/sqlhosts.odbc

export ODBCINI=/etc/odbc.ini
"""
# /etc/odbc.ini
"""
[ODBC Data Sources]
utf8=GBase ODBC DRIVER
;
; Define ODBC Database Driver's Below - Driver Configuration Section
;
[utf8]
Driver=/home/gbase8s/lib/cli/iclis09b.so
Description=GBase ODBC DRIVER
Database=test
LogonID=gbasedbt
pwd=xxx
Servername=gbase01
CursorBehavior=0
CLIENT_LOCALE=zh_CN.utf8
DB_LOCALE=zh_CN.utf8
GL_USEGLU=1
TRANSLATIONDLL=/home/gbase8s/lib/esql/igo4a304.so
;
; UNICODE connection Section
;
[ODBC]
;uncomment the below line for UNICODE connection
;UNICODE=UCS-4
;
; Trace file Section
;
Trace=0
TraceFile=/tmp/odbctrace.out
InstallDir=/extra/gbasedbt
TRACEDLL=idmrs09a.so

"""
# /etc/odbcinst.ini
"""
[ODBC Drivers]  
GBase ODBC DRIVER=Installed  
[GBase ODBC Driver]  
Driver=/home/gbase8s/lib/cli/iclit09b.so  
Setup=/home/gbase8s/lib/cli/iclit09b.so  
APILevel=1  
ConnectFunctions=YYY  
DriverODBCVer=03.51  
FileUsage=0  
SQLLevel=1  
smProcessPerConnect=Y
"""

conn_str = (
                # 'DRIVER={utf8};'
                'DRIVER=/home/gbase8s/lib/cli/iclis09b.so;'
                # 'SERVER=gbase01;'
                'SERVER=192.168.3.225,9088;'
                'DATABASE=test;'
                'UID=gbasedbt;'
                'PWD=xxx;'
                'SERVERNAME=gbase01;'
                'CLIENT_LOCALE=zh_CN.utf8;'
                'DB_LOCALE=zh_CN.utf8;'
            )


dsn = "DSN=utf8;PWD=xxx"
try:

   conn = pyodbc.connect(conn_str)
except Exception as e:
   print(e)
   # conn=pyodbc.connect('DRIVER=/home/gbase8s/lib/cli/iclis09b.so;SERVER=192.168.3.225,9088;DATABASE=test;UID=gbasedbt;PWD=xxx;')
   conn = pyodbc.connect(dsn)

conn.setencoding(encoding='UTF-8')
cursor = conn.cursor()  
# 执行个查询语句  
cursor.execute('drop table if exists tpyodbc;')
cursor.execute('create table tpyodbc(id serial, name varchar(60), sex char(3));')
cursor.execute("insert into tpyodbc(name,sex) values('张三','男')")
cursor.execute('insert into tpyodbc(name,sex) values("韩美美","女")')
conn.commit()

cursor1 = conn.cursor()
cursor1.execute('select id,name,sex from tpyodbc')
rows = cursor1.fetchall()
 
# 获取结果集  
# rows = cursor.fetchall()  
for row in rows:  
   print(row)
